﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Lswweb.Model;
using Lswweb.IDAL;

namespace Lswweb.SqlServerDAL
{
    public class TreeTableDAL : ITreeTable<CategoryInfo>
    {
        protected string tableName;
        protected Builder<CategoryInfo> treeTableBuilder;
        protected string orderBy = "DisplayOrder asc,Id desc";

        public TreeTableDAL(TreeTableType type)
        {
            tableName = type.ToString();

            treeTableBuilder = new Builder<CategoryInfo>(tableName,"Id");
            treeTableBuilder.CreateModel += new ReaderBind<CategoryInfo>(ReaderBind);
            treeTableBuilder.GetParameters += new ParametersBuilder<CategoryInfo>(GetParameters);
        }

        #region ITreeTable<CategoryInfo> 成员

        public CategoryInfo GetModel(int id)
        {
            if (id <= 0)
                return null;
            return treeTableBuilder.GetModel(id);
        }

        public string GetPath(int id)
        {
            if (id <= 0)
                return string.Empty;

            string where = "Id=@Id";
            IDataParameter parameter = DataAccess.CreateParameter("@Id",id);
            return (string)treeTableBuilder.GetFieldValue("[Path]",where,parameter);
        }

        public IList<object> GetPathText(int id)
        {
            string paths = GetPath(id).Replace("/",",");
            if (string.IsNullOrEmpty(paths))
                return new List<object>();
            return treeTableBuilder.GetFieldValue("Title","Id in (" + paths + ")","id",null);
        }

        public IList<CategoryInfo> GetModels()
        {
            string sql = "select * from  " + tableName;
            return treeTableBuilder.GetModels(sql, null);
        }
        public IList<CategoryInfo> GetModels(string ver)
        {
            string sql = "select * from  " + tableName + " where Ver=@Ver order by " + orderBy;
            return treeTableBuilder.GetModels(sql, DataAccess.CreateParameter("@Ver", ver));
        }

        public bool Update(CategoryInfo t)
        {
            return treeTableBuilder.Update(
                t,
                "Id=@Id",
                "Title",
                "Description",
                "Status");
        }

        public int Insert(CategoryInfo t)
        {

            int id=treeTableBuilder.Insert(
                t,
                "ParentId",
                "Title",
                "Status",
                "Ver",
                "Description");

            if (id > 0)
            {
                UpdateDisplayOrder(id, t.DisplayOrder);
                UpdatePath(id);
            }

            return id;
        }

        public bool Delete(int id)
        {
            if (id <= 0)
                return false;

            StringBuilder sql=new StringBuilder();
            sql.Append("declare @Path varchar(64);");
            sql.Append("select @Path=[Path] + '/' from " + tableName + " where Id=@Id;");
            sql.Append("delete " + tableName + " where [Path] + '/' like @Path + '%';");

            IDataParameter parameter = DataAccess.CreateParameter("@Id", id);
            
            return Convert.ToBoolean(DataAccess.ExecuteNonQuery(sql.ToString(),parameter));
        }

        public bool ExchangeDisplayOrder(int id, int id2)
        {
            return DataAccess.ExchangeFieldValue(tableName, "DisplayOrder", "smallint", "Id", id, id2);
        }

        public bool UpdateDisplayOrder(int id, int newDisplayOrder)
        {
            if (id <= 0)
                return false;

            IDataParameter[] parameters = new IDataParameter[2];
            parameters[0] = DataAccess.CreateParameter("@Id",id);
            parameters[1] = DataAccess.CreateParameter("@DisplayOrder", newDisplayOrder);
            string where = "ParentId=(select ParentId from " + tableName + " where Id=@Id)";
            return DataAccess.UpdateDisplayOrder(tableName, "DisplayOrder", "Id", where, newDisplayOrder, parameters);
        }

        public bool UpdateParentId(int id, int newParentId)
        {
            if (id <= 0)
                return false;

            StringBuilder sql = new StringBuilder();
            sql.Append("declare @OldParentId int;declare @OldDisplayOrder int;declare @MaxDisplayOrder int;");
            sql.Append("select @OldParentId = ParentId,@OldDisplayOrder=DisplayOrder from " + tableName + " where Id=@Id;");
            sql.Append("if @OldParentId = @ParentId begin return; end;");
            sql.Append("select @MaxDisplayOrder=max(displayorder) from " + tableName + " where parentId=@ParentId;");
            sql.Append("if @MaxDisplayOrder is null begin set @MaxDisplayOrder=0 end;");
            sql.Append("update " + tableName + " set ParentId=@ParentId,DisplayOrder=@MaxDisplayOrder + 1 from " + tableName + " where Id=@Id;");
            sql.Append("update " + tableName + " set DisplayOrder=DisplayOrder-1 where ParentId=@OldParentId and DisplayOrder>@OldDisplayOrder;");

            IDataParameter[] parameters = { DataAccess.CreateParameter("@Id",id),
                                            DataAccess.CreateParameter("@ParentId",newParentId)};

            bool label= Convert.ToBoolean(DataAccess.ExecuteNonQuery(sql.ToString(),parameters));
            if (label)
                UpdatePath(id);
            return label;
        }

        public bool UpdateField(int id, string filedName,object value)
        {
            return treeTableBuilder.UpdateFields(filedName, id, value);
        }

        public object GetFieldValue(int id,string fieldName)
        {
            return treeTableBuilder.GetFieldValue(fieldName, id);
        }

        /// <summary>
        /// 更新path字段
        /// </summary>
        /// <param name="id">要更新的记录Id</param>
        protected bool UpdatePath(int id)
        {
            if (id <= 0)
                return false;

            StringBuilder sql = new StringBuilder();
            sql.Append("declare @NewPath varchar(1024);");
            sql.Append("declare @ParentId int;");
            sql.Append("select @ParentId=ParentId from " + tableName + " where Id=@Id;");
            sql.Append("if @ParentId=0");
            sql.Append(" begin set @NewPath= cast(@Id as varchar(64)) end");
            sql.Append(" else");
            sql.Append(" begin select @NewPath=[Path] + '/' + cast(@Id as varchar(64)) from " + tableName + " where Id=@ParentId end");
            sql.Append(" update " + tableName + " set [Path]=@NewPath" + " where Id=@Id;");

            IDataParameter parameter = DataAccess.CreateParameter("@Id", id);

            return Convert.ToBoolean(DataAccess.ExecuteNonQuery(sql.ToString(), parameter));
        }

        #endregion

        #region Protected Methods

        public IDataParameter[] GetParameters(CategoryInfo model)
        {
            return new IDataParameter[]{
                DataAccess.CreateParameter("@Id", model.Id),
                DataAccess.CreateParameter("@ParentId", model.ParentId),
                DataAccess.CreateParameter("@Title", model.Title),
                DataAccess.CreateParameter("@DisplayOrder", model.DisplayOrder),
                DataAccess.CreateParameter("@Ver", model.Ver),
                DataAccess.CreateParameter("@Status", model.Status),
                DataAccess.CreateParameter("@Path", string.IsNullOrEmpty(model.Path)?null:model.Path),
                DataAccess.CreateParameter("@Description", string.IsNullOrEmpty(model.Description)?null:model.Description)};
        }

        public CategoryInfo ReaderBind(Hashtable dataReader)
        {
            CategoryInfo model = new CategoryInfo();

            if (dataReader.ContainsKey("Id"))
                model.Id = Convert.ToInt32(dataReader["Id"]);

            if (dataReader.ContainsKey("ParentId"))
                model.ParentId = Convert.ToInt32(dataReader["ParentId"]);

            if (dataReader.ContainsKey("Title"))
                model.Title = dataReader["Title"].ToString();

            if (dataReader.ContainsKey("DisplayOrder"))
                model.DisplayOrder = Convert.ToInt32(dataReader["DisplayOrder"]);

            if (dataReader.ContainsKey("Path"))
                model.Path = dataReader["Path"].ToString();

            if (dataReader.ContainsKey("Status"))
                model.Status = (bool)dataReader["Status"];

            if (dataReader.ContainsKey("Ver"))
                model.Ver = dataReader["Ver"].ToString();

            if (dataReader.ContainsKey("Icon"))
                model.Icon = dataReader["Icon"].ToString();

            if (dataReader.ContainsKey("Description"))
                model.Description = dataReader["Description"].ToString();

            return model;
        }

        #endregion
    }
}
